﻿Imports ComponentFactory.Krypton.Toolkit

Imports System.Data.OleDb
Imports WiseLibraries.Net
Imports WiseLibraries.Strings
Imports System.Text


Public Class CadastroAutomovel
    Inherits KryptonForm

    Private conexao As OleDbConnection
    Private command As OleDbCommand
    Private dataSet As DataSet
    Private dataview As DataView
    Private dataAdapter As OleDbDataAdapter
    Private cmdBuilder As OleDbCommandBuilder
    Private auto As Integer
    Private strsalvar As String
    Private tipocadastro As String
    Private sb As StringBuilder
    Private camposErrados As ArrayList 'Para a validação
    Private colorCleaner As ComponentColorCleaner

#Region "Atributos"
    Private proprietario As String = Nothing
    Private placa As String = Nothing
    Private observacao As String = Nothing
    Private modelo As String = Nothing
    Private ano As Integer = Nothing
    Private chassi As String = Nothing
    Private potencia As String = Nothing
    Private combustivel As String = Nothing
    Private estado As String = Nothing
    Private montadora As String = Nothing
    Private cor As String = Nothing

#End Region

    Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.
        Me.carregarCor()
        Me.carregarEstados()
        Me.carregarProprietario()
        Me.carregarMontadora()
        Me.carregarCombustivel()
        Me.carregarModelo()
        tipocadastro = "novo"
        cbproprietario.SelectedIndex = 0
        cbestado.Text = "SP"
        cbcombustivel.SelectedIndex = 0
        cbcor.SelectedIndex = 0
        cbmodelo.SelectedIndex = 0
        cbmontadora.SelectedIndex = 0

        conexao.Close()
        colorCleaner = New ComponentColorCleaner
        colorCleaner.add(Me.Controls, Assistente.COR_NORMAL)

    End Sub
    Sub New(ByVal auto As Integer)

        ' This call is required by the Windows Form Designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.
        tipocadastro = "editar"
        Me.Text = "Wotan - Edição de cadastro de automóvel"
        Me.carregarCor()
        Me.carregarEstados()
        Me.carregarProprietario()
        Me.carregarMontadora()
        Me.carregarCombustivel()
        Me.carregarModelo()

        btnlimpar.Visible = False
        btnsalvar.Left = btnlimpar.Left

        conexao = FabricaDeConexoes.CriarConexao()
        conexao.Open()
        command = New OleDbCommand("SELECT tblAuto.codAuto" & _
                                        ", tblModeloAuto.descricaoModeloAuto " & _
                                        ", tblMontadoraAuto.descricaomontadoraAuto " & _
                                        ", chassiAuto " & _
                                        ", tblCliente.nomeCliente " & _
                                        ", placaAuto " & _
                                        ", tblCombustivel.descricaoCombustivel " & _
                                        ", anofabricacaoAuto  " & _
                                        ", observacaoAuto  " & _
                                        ", tblCorAuto.descricaocorAuto  " & _
                                        ", potenciaMotor  " & _
                                        ", tblEstado.siglaEstado  " & _
                                        " FROM ((((((tblAuto" & _
                                        " LEFT JOIN tblModeloAuto ON tblModeloAuto.codmodeloAuto = tblAuto.codmodeloAuto)" & _
                                        " LEFT JOIN tblMontadoraAuto ON tblMontadoraAuto.codmontadoraAuto = tblAuto.codmontadoraAuto)" & _
                                        " LEFT JOIN tblCliente ON tblCliente.codCliente = tblAuto.codCliente)" & _
                                        " LEFT JOIN tblCombustivel ON tblCombustivel.codCombustivel = tblAuto.codCombustivel) " & _
                                        " LEFT JOIN tblCorAuto ON tblCorAuto.codcorAuto = tblAuto.codcorAuto)" & _
                                        " LEFT JOIN tblEstado ON tblEstado.codEstado = tblAuto.codEstado) where codAuto =" & auto, conexao)


        Dim dr As OleDbDataReader = command.ExecuteReader()

        dr.Read()

        Debug.WriteLine(command.CommandText)
        If (dr("codAuto") = auto) Then

            Me.auto = auto

            txtano.Text = dr("anofabricacaoAuto")
            cbproprietario.Text = dr("nomeCliente")
            cbmodelo.Text = dr("descricaoModeloAuto")
            txtplaca.Text = dr("placaAuto")
            cbcor.Text = dr("descricaocorAuto")
            cbmontadora.Text = dr("descricaomontadoraAuto")
            cbcombustivel.Text = dr("descricaoCombustivel")
            txtchassi.Text = dr("chassiAuto")
            cbestado.Text = dr("siglaEstado")
            txtpotencia.Text = dr("potenciaMotor")

            txtobservacao.Text = dr("observacaoAuto")
        End If

        conexao.Close()
        colorCleaner = New ComponentColorCleaner
        colorCleaner.add(Me.Controls, Assistente.COR_NORMAL)
    End Sub
    Private Sub carregarProprietario()

        Try
            Dim strproprietario As String = "SELECT * FROM tblCliente"
            conexao = FabricaDeConexoes.CriarConexao()
            command = New OleDbCommand(strproprietario, conexao)
            dataAdapter = New OleDbDataAdapter(command)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblCliente")

            cbproprietario.DataSource = dataSet.Tables("tblCliente")
            cbproprietario.DisplayMember = "nomeCliente"
            cbproprietario.ValueMember = "codCliente"

        Catch ex As Exception
            Debug.WriteLine(ex)
        End Try
    End Sub

    'criar sub para carregar montadora
    Private Sub carregarMontadora()

        Try
            Dim strmontadora As String = "SELECT * FROM tblMontadoraAuto"
            conexao = FabricaDeConexoes.CriarConexao()
            command = New OleDbCommand(strmontadora, conexao)
            dataAdapter = New OleDbDataAdapter(command)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblMontadoraAuto")


            cbmontadora.DataSource = dataSet.Tables("tblMontadoraAuto")
            cbmontadora.DisplayMember = "descricaomontadoraAuto"
            cbmontadora.ValueMember = "codmontadoraAuto"

        Catch ex As Exception
            Debug.WriteLine(ex)
        End Try
    End Sub
    'sub para carregar combustivel
    Private Sub carregarCombustivel()

        Try
            Dim strcombustivel As String = "SELECT * FROM tblCombustivel"
            conexao = FabricaDeConexoes.CriarConexao()
            command = New OleDbCommand(strcombustivel, conexao)
            dataAdapter = New OleDbDataAdapter(command)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblCombustivel")

            cbcombustivel.DataSource = dataSet.Tables("tblCombustivel")
            cbcombustivel.DisplayMember = "descricaoCombustivel"
            cbcombustivel.ValueMember = "codCombustivel"

        Catch ex As Exception
            Debug.WriteLine(ex)
        End Try
    End Sub

    'carregar sub combo estado
    Private Sub carregarEstados()
        Try
            Dim strQueryEstado As String = "SELECT * FROM tblEstado"
            conexao = FabricaDeConexoes.CriarConexao()
            command = New OleDbCommand(strQueryEstado, conexao)
            dataAdapter = New OleDbDataAdapter(command)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblEstado")
            cbestado.DataSource = dataSet.Tables("tblEstado")
            cbestado.DisplayMember = "siglaEstado"
        Catch ex As Exception
            Debug.WriteLine("Erro: " & ex.Message)
        Finally
            conexao.Close()
            command.Dispose()
            dataAdapter.Dispose()
        End Try
    End Sub

    'carregar combo cor
    Private Sub carregarCor()
        Try
            Dim strcor As String = "SELECT * FROM tblCorAuto"
            conexao = FabricaDeConexoes.CriarConexao()
            command = New OleDbCommand(strcor, conexao)
            dataAdapter = New OleDbDataAdapter(command)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblCorAuto")

            cbcor.DataSource = dataSet.Tables("tblCorAuto")
            cbcor.DisplayMember = "descricaocorAuto"
            cbcor.ValueMember = "codcorAuto"

        Catch ex As Exception
            Debug.WriteLine(ex)
        End Try
    End Sub

    Private Sub btncancelar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancelar.Click
        Me.Dispose()
    End Sub

    Private Sub carregarModelo()
        Try
            Dim strproprietario As String = "SELECT * FROM tblModeloAuto"
            conexao = FabricaDeConexoes.CriarConexao()
            command = New OleDbCommand(strproprietario, conexao)
            dataAdapter = New OleDbDataAdapter(command)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblModeloAuto")

            cbmodelo.DataSource = dataSet.Tables("tblModeloAuto")
            cbmodelo.DisplayMember = "descricaoModeloAuto"
            cbmodelo.ValueMember = "codmodeloAuto"

        Catch ex As Exception
            Debug.WriteLine(ex)
        End Try
    End Sub

    Private Sub verificarCampos()

        If (sb.Length > 0) Then
            Dim c() As Control
            c = camposErrados.ToArray(GetType(Control))
            Assistente.trocaCorCampos(c, Assistente.COR_ERRO)
            KryptonMessageBox.Show("Há erros nos seguintes campos:" & vbNewLine & sb.ToString() & vbNewLine & "Verifique e tente novamente.", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End If
    End Sub

    Private Function validarAutomovel() As Boolean
        Dim val As New RegexValidator()

        camposErrados = New ArrayList()
        sb = New StringBuilder()
        'Valida Proprietário

        If Not (String.IsNullOrEmpty(cbproprietario.Text)) Then
            'proprietario = 1
            Try
                proprietario = DirectCast(cbproprietario.SelectedItem, DataRowView).Item("codCliente")
            Catch ex As Exception
                Debug.WriteLine(ex)
            End Try
        Else
            camposErrados.Add(cbproprietario)
            sb.Append("Proprietario" & vbNewLine)
        End If

        'Valida(placa)
        If (txtplaca.MaskCompleted) Then
            placa = txtplaca.Text
        Else
            camposErrados.Add(txtplaca)
            sb.Append("Placa" & vbNewLine)
        End If

        'Valida(montadora)
        If Not (String.IsNullOrEmpty(cbmontadora.Text)) Then
            ' montadora = 1
            Try
                montadora = DirectCast(cbmontadora.SelectedItem, DataRowView).Item("codmontadoraAuto")
            Catch ex As Exception
                Debug.WriteLine(ex)
                Debug.WriteLine(montadora)
            End Try
        Else
            camposErrados.Add(cbmontadora)
            sb.Append("Montadora" & vbNewLine)
        End If

        'Valida(combustivel)
        If Not (String.IsNullOrEmpty(cbcombustivel.Text)) Then
            combustivel = DirectCast(cbcombustivel.SelectedItem, DataRowView).Item("codCombustivel")
        Else
            camposErrados.Add(cbcombustivel)
            sb.Append("Combustivel" & vbNewLine)
        End If

        ' Valida(estado)
        If Not (String.IsNullOrEmpty(cbestado.Text)) Then
            estado = DirectCast(cbestado.SelectedItem, DataRowView).Item("codEstado")
        Else
            camposErrados.Add(cbestado)
            sb.Append("Estado" & vbNewLine)
        End If
       
        ' Valida(modelo)
        If Not (String.IsNullOrEmpty(cbmodelo.Text)) Then
            modelo = DirectCast(cbmodelo.SelectedItem, DataRowView).Item("codmodeloAuto")
        Else
            camposErrados.Add(cbmodelo)
            sb.Append("Modelo" & vbNewLine)
        End If


        ' Valida(cor)
        If Not (String.IsNullOrEmpty(cbcor.Text)) Then
            cor = DirectCast(cbcor.SelectedItem, DataRowView).Item("codcorAuto")
        Else

            camposErrados.Add(cbcor)
            sb.Append("Cor" & vbNewLine)
        End If

        'Valida(ano)
        If Not (String.IsNullOrEmpty(txtano.Text)) Then
            ano = txtano.Text
        Else
            camposErrados.Add(txtano)
            sb.Append("Ano" & vbNewLine)
        End If

        'Valida(chassi)
        If Not (String.IsNullOrEmpty(txtchassi.Text)) Then
            chassi = txtchassi.Text
        Else
            camposErrados.Add(txtchassi)
            sb.Append("Chassi" & vbNewLine)
        End If

        ' Valida(Potência)
        If Not (String.IsNullOrEmpty(txtpotencia.Text)) Then
            potencia = txtpotencia.Text
        Else
            camposErrados.Add(txtpotencia)
            sb.Append("Potencia" & vbNewLine)
        End If

        ' Valida(observação)
        If Not (String.IsNullOrEmpty(txtobservacao.Text)) Then
            observacao = txtobservacao.Text
        Else
            camposErrados.Add(txtobservacao)
            sb.Append("Observacao" & vbNewLine)
        End If

        verificarCampos()

        If (sb.Length > 0) Then
            Return (False)
        Else
            Return (True)
        End If
    End Function

    Private Sub cadastrarAutomovel()

        If (validarAutomovel() = True) Then

            Dim sql As String = "INSERT INTO tblAuto(codmodeloAuto,codmontadoraAuto,chassiAuto, codCliente,placaAuto,codCombustivel,anofabricacaoAuto,observacaoAuto, codcorAuto,potenciaMotor, codEstado) VALUES('" & _
            modelo & "'," & _
            montadora & ",'" & _
            chassi & "'," & _
            proprietario & ",'" & _
            placa & "','" & _
            combustivel & "','" & _
            ano & "','" & _
            observacao & "','" & _
            cor & "','" & _
            potencia & "','" & _
            estado & "')"

            Debug.WriteLine(sql)
            Try
                conexao.Open()
                command = New OleDbCommand(sql, conexao)
                command.ExecuteNonQuery()
                Debug.WriteLine(sql)
                ' KryptonMessageBox.Show("Automóvel cadastrado com sucesso!", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Assistente.limparCampos(Me)

            Catch ex As Exception
                Debug.WriteLine(ex.Message)
                KryptonMessageBox.Show("Automóvel não cadastrado !", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End If
        conexao.Close()

    End Sub

    Private Sub btnsalvar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsalvar.Click
        If (tipocadastro = "novo") Then
            If (validarAutomovel()) Then
                cadastrarAutomovel()
                KryptonMessageBox.Show("Automóvel cadastrado com sucesso!", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Me.Dispose()
            End If

        ElseIf (tipocadastro = "editar") Then
            If (validarAutomovel()) Then
                AtualizarAuto()
                KryptonMessageBox.Show("Automóvel atualizado com sucesso!", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Me.Dispose()

            End If
        End If
    End Sub

    Private Sub btnlimpar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlimpar.Click
        
        Assistente.limparCampos(Me)
        Assistente.trocaCorTransition(Me)
        cbproprietario.SelectedIndex = 0
        cbestado.Text = "SP"
        cbcombustivel.SelectedIndex = 0
        cbcor.SelectedIndex = 0
        cbmodelo.SelectedIndex = 0
        cbmontadora.SelectedIndex = 0
    End Sub

    Private Sub btnaddmonta_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnaddmontadora.Click
        Dim editor As New EditorDeTabelaSimples("Adicionar ou Remover Montadora", conexao, "tblMontadoraAuto", "descricaomontadoraAuto")
        editor.ShowDialog()
        cbmontadora.DataBindings.Clear()
        cbmontadora.DataSource = Nothing
        cbmontadora.Refresh()

        carregarMontadora()
    End Sub


    Private Sub btnaddmodelo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnaddmodelo.Click
        Dim editor As New EditorDeTabelaSimples("Adicionar ou Remover Modelo", conexao, "tblModeloAuto", "descricaoModeloAuto")
        editor.ShowDialog()
        cbmodelo.DataBindings.Clear()
        cbmodelo.DataSource = Nothing
        cbmodelo.Refresh()

        carregarModelo()
    End Sub

    Private Sub btnaddcor_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnaddcombustivelcarro.Click
        Dim editor As New EditorDeTabelaSimples("Adicionar ou Remover Combustível", conexao, "tblCombustivel", "descricaoCombustivel")
        editor.ShowDialog()
        cbcombustivel.DataBindings.Clear()
        cbcombustivel.DataSource = Nothing
        cbcombustivel.Refresh()

        carregarCombustivel()
    End Sub

    Private Sub btnaddcombustivel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnaddcor.Click
        Dim editor As New EditorDeTabelaSimples("Adicionar ou Remover Cor", conexao, "tblCorAuto", "descricaocorAuto")
        editor.ShowDialog()
        cbcor.DataBindings.Clear()
        cbcor.DataSource = Nothing
        cbcor.Refresh()

        carregarCor()
    End Sub

    Private Sub AtualizarAuto()
        If (validarAutomovel() = True) Then

            'Debug.WriteLine(auto)
            Dim sql As String = "UPDATE tblAuto SET codmodeloAuto = " & modelo & ", codmontadoraAuto = " & montadora & ", chassiAuto = '" & chassi & "', codCliente = " & proprietario & ", placaAuto = '" & placa & "', codCombustivel= " & combustivel & ", anofabricacaoAuto = " & ano & ", observacaoAuto = '" & observacao & "', codcorAuto = " & cor & ", potenciaMotor = '" & potencia & "', codEstado = " & estado & " WHERE codAuto = " & auto

            'Debug.WriteLine(auto)

            Debug.WriteLine(sql)
            Try
                conexao.Open()
                command = New OleDbCommand(sql, conexao)
                command.ExecuteNonQuery()
                Debug.WriteLine(sql)
                Assistente.limparCampos(Me)

            Catch ex As Exception
                Debug.WriteLine(ex.Message)
                KryptonMessageBox.Show("Automóvel não cadastrado !", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End If
        conexao.Close()

    End Sub

End Class